// ~ CopyRight © 2012 USTC SINOVATE  SOFTWARE CO.LTD All Rights Reserved.
package com.cmcc.zysoft.sysmanage.dao;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.cmcc.zysoft.infocollection.model.Company;
import com.cmcc.zysoft.spring.security.model.User;
import com.cmcc.zysoft.spring.security.util.SecurityContextUtil;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;

/**
 * @author li.menghua
 * @date 2012-11-28 下午3:40:06
 */
@Repository
public class CompanyDao extends HibernateBaseDaoImpl<Company, String> {

	/**
	 * 属性名称：jdbcTemplate <br/>
	 * 类型：JdbcTemplate
	 */
	@Autowired
	private JdbcTemplate jdbcTemplate;

	/**
	 * 更新公司信息
	 * 
	 * @return
	 */
	@Transactional
	public void updateCompany(Company company) {
		this.update(company);
	}

	/**
	 * treegrid 获取公司树
	 * 
	 * @param parentCompanyId
	 * @return
	 */
	public List<Map<String, Object>> getCompanyTree(String parentCompanyId,
			String companyId) {
		String sql = "SELECT comp.*,"
				+ "(case comp.have_child_company when 'N' then 'open' when 'Y' then 'closed' else 'open' end) as state,"
				+ "(case comp.have_child_company when 'N' then true when 'Y' then false else true end) as isLeaf,"
				+ "'folder' as iconCls ";
		sql += " FROM tb_c_company comp";
		sql += " WHERE comp.del_flag='0' " + " and comp.parent_company_id=?";
		if (StringUtils.hasText(companyId)) {
			sql += " and comp.company_id='" + companyId + "'";
		}
		return this.jdbcTemplate.queryForList(sql, parentCompanyId);
	}

	/**
	 * tree 获取公司树
	 * 
	 * @param parentCompanyId
	 * @param companyId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getCompanyTreeHql(String companyId) {
		String hql = "select new Map(" + "com.companyId as id,"
				+ "com.companyName as text,"
				+ "com.haveChildCompany as haveChildCom) "
				+ "from Company com where com.delFlag='0'";
		if (StringUtils.hasText(companyId)) {
			hql += " and com.companyId='" + companyId + "'";
		}
		return this.findByHQL(hql);
	}

	/**
	 * combortree 获取公司树
	 * 
	 * @author yandou
	 * @param parentCompanyId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> companyTree(String parentCompanyId) {
		// 判断是否超级管理员，是获取全部公司
		User user = SecurityContextUtil.getCurrentUser();
		String currentCompanyId = "";
		if (!"0".equals(user.getRoles().get(0).getRoleCode())) {
			currentCompanyId = SecurityContextUtil.getCompanyId();
		}

		String hql = "select new Map(com.companyId as id,com.companyName as text,com.haveChildCompany as haveChildCom) from Company com where com.parentCompanyId = ? and com.delFlag='0' ";
		if (StringUtils.hasText(currentCompanyId)) {
			hql += " and com.companyId = '" + currentCompanyId + "'";
		}
		return this.findByHQL(hql,
				StringUtils.hasText(parentCompanyId) ? parentCompanyId : "0");
	}

	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> companyTree(String parentCompanyId, User user) {
		String currentCompanyId = "";
		if(!"0".equals(user.getRoles().get(0).getRoleCode())){
			currentCompanyId = SecurityContextUtil.getCompanyId();
		}
		
		String hql = "select new Map(com.companyId as id,com.companyName as text,com.haveChildCompany as haveChildCom) from Company com where com.parentCompanyId = ? and com.delFlag='0' ";
		if(StringUtils.hasText(currentCompanyId)){
			hql  += " and com.companyId = '"+currentCompanyId+"'"; 
		}
		return this.findByHQL(hql,StringUtils.hasText(parentCompanyId)?parentCompanyId:"0");
	}
	
	/**
	 * 根据company_id parent_company_id查找属于同一父公司下其他子公司数量
	 * 
	 * @param companyId
	 * @param exceptId
	 * @return
	 */
	public int getSubComCountExcept(String companyId, String exceptId) {
		String sql = "select count(*) from tb_c_company where parent_company_id = ? and company_id <> ? and del_flag <> '1'";
		return this.jdbcTemplate.queryForInt(sql, companyId, exceptId);
	}

	/**
	 * 查找公司
	 * 
	 * @param companyName
	 * @return
	 */
	public List<Map<String, Object>> searchComp(String companyName) {
		String sql = "SELECT * FROM tb_c_company comp WHERE comp.del_flag='0' AND comp.company_name like '%"
				+ companyName.trim() + "%'";
		return this.jdbcTemplate.queryForList(sql);
	}

	/**
	 * 通过公司Id查找公司
	 * 
	 * @param companyId
	 * @return
	 */
	public List<Map<String, Object>> searchCompByCompId(String companyId) {
		String sql = "SELECT * FROM tb_c_company comp WHERE comp.del_flag='0' AND comp.company_id = ? ";
		return this.jdbcTemplate.queryForList(sql, companyId);
	}

	/**
	 * 添加公司
	 * 
	 * @param company
	 * @return 返回ID
	 */
	@Transactional
	public String addCompany(Company company) {
		return this.save(company);
	}

	/**
	 * 获取所有公司列表
	 * 
	 * @return 返回类型：List<Company>
	 */
	public List<Company> getAllCompany() {
		return this.loadAll();
	}

	/**
	 * 根据员工ID查找该员工的公司ID
	 * 
	 * @param employeeId
	 * @return 公司ID 返回类型：String
	 */
	@SuppressWarnings("unchecked")
	public String getCompanyIdByEmployeeId(String employeeId) {
		String hql = "select \n" + "	c.*\n" + "FROM\n" + "	Employee as e,\n"
				+ "	Company as c,\n" + "	Department as d\n" + "where\n"
				+ "	e.departmentId = d.departmentId AND\n"
				+ "	d.companyId = c.companyId AND\n" + "	e.employeeId = ? ";
		List<Company> companys = this.findByHQL(hql, employeeId);
		return companys.isEmpty() ? null : companys.get(0).getCompanyId();

	}

	/**
	 * 根据员工ID获取其所属公司的ID 
	 * @author yuan.fengjian@ustcinfo.com
	 * @param employeeId
	 */
	public String getComId8EmpId(String employeeId) {
		String sql = "select c.* from tb_c_employee e, tb_c_company c, tb_c_department d where e.department_id = d.department_id " +
			" and d.company_id = c.company_id and e.employee_id = ?";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql, employeeId);
		return list.get(0).get("company_id").toString();
	}

	/**
	 * 根据公司ID获取其所属公司信息 
	 * @author yuan.fengjian@ustcinfo.com
	 * @param companyId
	 */
	public List<Map<String, Object>> getCom8ComId(String companyId) {
		String sql = "select a.company_id as id, a.company_name as text, a.have_child_company as haveChildCom, a.* from tb_c_company a" +
			" where a.company_id = ?";
		return this.jdbcTemplate.queryForList(sql, companyId);
	}

	/**
	 * @author yandou
	 * 从菜单中获取需要同步新增菜单的公司信息
	 * @return
	 */
	public List<Map<String, Object>> getSyschroMenuCompany() {
		String sql = "select " +
				"m.company_id," +
				"p.prd_code " +
				"from " +
				"tb_c_menu m,tb_c_company c,tb_i_adc_provisioning_ict p " +
				"where m.company_id = c.company_id and c.company_code = p.ec_code " +
				"group by company_id";
		return this.jdbcTemplate.queryForList(sql);
	}
}
